package com.wry.file.controller;

import cn.hutool.core.util.URLUtil;
import com.alibaba.excel.EasyExcelFactory;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.write.metadata.WriteSheet;
import com.alibaba.excel.write.metadata.fill.FillConfig;
import com.wry.file.utils.PoiTemplateExportUtil;
import lombok.Data;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.RestController;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.http.HttpServletResponse;
import java.io.InputStream;
import java.util.*;
import java.util.stream.Collectors;

/**
 * <h1>  <h1>
 *
 * @author wry
 * @since 2024/9/12 10:18
 */
@RequestMapping("file")
@RestController
public class ExcelController {

    /**
     * 传入一个带有模板的excel，填充数据后导出一份excel
     * 基于 easy-excel，可以完成数据填充，合并不可以
     */
    @PostMapping("excel-01")
    public void excel01(@RequestParam("file") MultipartFile file, HttpServletResponse response) throws Exception{
        final InputStream inputStream = file.getInputStream();
        try  {
            Goods goods = new Goods();
            goods.setGoodsName("这是一个品");
            final ExcelWriter excelWriter = EasyExcelFactory
                    .write(response.getOutputStream())
                    .withTemplate(inputStream)
                    .build();
            final WriteSheet writeSheet = EasyExcelFactory.writerSheet("配方表 (3)").build();
            final FillConfig fillConfig = FillConfig.builder().forceNewRow(Boolean.FALSE).build();
            // 基础数据
            excelWriter.fill(goods, fillConfig, writeSheet);
            final FillConfig fillConfig2 = FillConfig.builder().forceNewRow(Boolean.TRUE).build();
            // 需要循环的数据
            excelWriter.fill(getInfoList(), fillConfig2, writeSheet);
            response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
            response.setHeader("Content-Disposition", "attachment;filename=" + URLUtil.encode("excel-test01") + ".xlsx");
            excelWriter.finish();
        }catch (Exception e) {
            e.printStackTrace();
        }
    }

    public static List<Goods.Info> getInfoList() {
        List<Goods.Info> infoList = new ArrayList<>();
        for (int i = 1; i <= 10; i++) {
            Goods.Info info = new Goods.Info();
            info.setNo(i);
            info.setCode("code-" + i);
            info.setName("商品名" + i);
            info.setCnName("标准中文名称" + i);
            info.setEnName("INCI名称/英文名称" + i);
            info.setHl("原料含量（%）" + i);
            info.setBfb("复配百分比（%）" + i);
            info.setCfhl("实际成分含量（%）" + i);
            info.setMd("使用目的" + i);
            info.setXb("相别" + i);
            info.setXh("在《已使用原料目录》中的序号" + i);
            info.setScs("生产商" + i);
            info.setBsm("报送码"+i);
            info.setRemark("备注"+i);
            infoList.add(info);
        }

        int i = 3;
        Goods.Info info = new Goods.Info();
        info.setNo(i);
        info.setCode("code-" + i);
        info.setName("商品名" + i);
        info.setCnName("标准中文名称" + i);
        info.setEnName("INCI名称/英文名称" + i);
        info.setHl("原料含量（%）" + i);
        info.setBfb("复配百分比（%）" + i);
        info.setCfhl("实际成分含量（%）" + i);
        info.setMd("使用目的" + i);
        info.setXb("相别" + i);
        info.setXh("在《已使用原料目录》中的序号" + i);
        info.setScs("生产商" + i);
        info.setBsm("报送码"+i);
        info.setRemark("备注"+i);
        infoList.add(info);

        int j = 1;
        Goods.Info info1 = new Goods.Info();
        info1.setNo(j);
        info1.setCode("code-" + j);
        info1.setName("商品名" + j);
        info1.setCnName("标准中文名称" + j);
        info1.setEnName("INCI名称/英文名称" + j);
        info1.setHl("原料含量（%）" + j);
        info1.setBfb("复配百分比（%）" + j);
        info1.setCfhl("实际成分含量（%）" + j);
        info1.setMd("使用目的" + j);
        info1.setXb("相别" + j);
        info1.setXh("在《已使用原料目录》中的序号" + j);
        info1.setScs("生产商" + j);
        info1.setBsm("报送码"+ j);
        info1.setRemark("备注"+ j);
        infoList.add(info1);

        int y = 10;
        Goods.Info info10 = new Goods.Info();
        info10.setNo(y);
        info10.setCode("code-" + y);
        info10.setName("商品名" + y);
        info10.setCnName("标准中文名称" + y);
        info10.setEnName("INCI名称/英文名称" + y);
        info10.setHl("原料含量（%）" + y);
        info10.setBfb("复配百分比（%）" + y);
        info10.setCfhl("实际成分含量（%）" + y);
        info10.setMd("使用目的" + y);
        info10.setXb("相别" + y);
        info10.setXh("在《已使用原料目录》中的序号" + y);
        info10.setScs("生产商" + y);
        info10.setBsm("报送码"+ y);
        info10.setRemark("备注"+ y);
        infoList.add(info10);

        return infoList.stream().sorted(Comparator.comparing(Goods.Info::getNo)).collect(Collectors.toList());
    }

    @Data
    public static class Goods {
        private String goodsName;

        @Data
        public static class Info {

            private Integer no;

            private String code;

            private String name;

            private String cnName;

            private String enName;

            private String hl;

            private String bfb;

            private String cfhl;

            private String md;

            private String xb;

            private String xh;

            private String scs;

            private String bsm;

            private String remark;
        }
    }


    /**
     * poi
     *
     * excel模板导入，导出，动态合并单元格
     */
    @PostMapping("excel-02")
    public void excel02(@RequestParam("file") MultipartFile file, HttpServletResponse response) throws Exception{
        response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
        response.setHeader("Content-Disposition", "attachment;filename=" + URLUtil.encode("excel-test-poi") + ".xlsx");

        Workbook workbook = new XSSFWorkbook(file.getInputStream());
        final Sheet sheet = workbook.getSheet("配方表 (3)");

        // 第二行（设置 goodsName）
        Row secondRow = sheet.getRow(1);
        Cell goodsNameCell = secondRow.getCell(0);
        goodsNameCell.setCellValue("这是一个货品名称");

        // 记录数据起始插入行的行号（第五行需要保留）
        // 从第四行开始插入数据
        int dataStartRowIndex = 3;

        final List<Goods.Info> infoList = getInfoList();

        // 移动原有的第五行及其以下的内容，向下移动infoList.size()行
        int lastRowNum = sheet.getLastRowNum();
        if (lastRowNum > dataStartRowIndex) {
            sheet.shiftRows(dataStartRowIndex + 1, lastRowNum, infoList.size() - 1, true, false);
        }
        PoiTemplateExportUtil.write(sheet, dataStartRowIndex, infoList);
        workbook.write(response.getOutputStream());
    }

    /**
     * easy-poi 模板导入导出
     */
    private void easyPoi() {
        // TODO easyPoi 和 poi-tl 都引用了poi，且他们之间版本有冲突
//        TemplateExportParams exportParams = new TemplateExportParams("file.getInputStream()");
//        Map<String, Object> map = new HashMap<>(2);
//        map.put("goodsName", "这是一个商品信息");
//        map.put("infoList", getInfoList());
//        try {
//            Workbook workbook = ExcelExportUtil.exportExcel(exportParams, map);
//            response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
//            response.setHeader("Content-Disposition", "attachment;filename=" + URLUtil.encode("easypoi-test02") + ".xlsx");
//            OutputStream outputStream = response.getOutputStream();
//            workbook.write(outputStream);
//            outputStream.flush();
//            outputStream.close();
//        } catch (Exception e) {
//            e.printStackTrace();
//        }
    }
}
